Shareware Grab Bag

home *** CD-ROM | disk | FTP | other *** search

/ Shareware Grab Bag / Shareware Grab Bag.iso / 002 / document.arc / DBASEII.DOC < prev next >

Wrap

Text File | 1984-03-10 | 14.3 KB | 331 lines

NOTES ON USE OF CCDB FILES WITH DBASEIItm These remarks are designed for the dBaseII user with a general familiarity with dBaseII but without experience in use of files created outside dBaseII. Underlined words in the examples below are as typed by a user. Data Base Set-up In order to work with a data set, dBaseII must first convert the data into its own format. Fortunately, this is a fairly straightforward process. The user should be aware, however, that these data in dBaseII format generally occupy 15 to 35 percent more space than in the comma-delimited format as provided by the Bureau. The following suggestions assume the user is working with a machine with two 320 or 360 kb floppy disk drives. 1. If you plan to work with all of a file which is 120 to 160 kb in comma-delimited format, transfer the incoming file onto the same disk as your dBaseII system using COPY or another systems utility prior to starting dBaseII. 2. "Create" the file structure for dBaseII. You will need to enter in a name, type and field length for every field on the incoming file. The following assumes you are using the incoming file CCDB01.TXT as is. . CREATE FILENAME: B:COUNTY01 (any name you select) FIELD NAME,TYPE,WIDTH,DECIMAL PLACES 001 GEOG8001,C,5 002 FLAG0011,N,1 003 LAND8001,N,7 004 FLAG0012,N,1 005 POPG7001,N,9 . . INPUT DATA NOW? NO The name for each field can be either the mnemonic label presented in the data dictionaries, or anything else you choose within dBaseII naming conventions (e.g. up to 10 characters long). The type is usually N (numeric) for flags and data items, and usually C (character string) for area names and codes. The width is the field size, and errors will result if too few characters are allocated. The data dictionary shows the maximum digits required nationwide for each item. Decimal places need not be specified since only integers are presented on the files (although some items have implied decimals). _____________________ dBaseII is a trademark of Ashton-Tate 2 You must list every field on the incoming file, including flags. If you want to extract only certain fields or merge fields from more than one file, this may be done with the UTILITYC program before entering dBaseII, or in dBaseII as discussed below in item 6. (Note: if you are using an abbreviated data dictionary created by the UTILITYC program, don't forget that this format fails to list area codes and flags even though they are still present in the data file.) 3. Load the incoming data into the structure you have just created. . USE B:COUNTY01 . APPEND FROM A:CCDB01.TXT DELIMITED If you want to enter in only certain kinds of records you may make that selection at this time. For example, APPEND FROM A:CCDB01.TXT FOR $(GEOG8001,1,2) = "06" DELIMITED would load only data for California (state code 06). This kind of geographic extraction can also be done in the UTILITYC program. Extraction based on characteristics can be done only in dBaseII, e.g., APPEND FROM A:CCDB01.TXT FOR POPG8001 >= 50000 DELIMITED. A bug in dBaseII is that occasionally the system fails to capture the last few records when appending from a comma delimited file. If you find that the number of records is slightly less than it should be (e.g., 3189 in a county file or 1008 in a city file), then you will need to take the following actions: a. Create a dummy data base with the same structure as the current data base using COPY STRUCTURE TO DUMMY. Enter that file with USE DUMMY. b. Create several dummy records, for example, using the APPEND command and entering in zeroes in each field. Enter COPY TO DUMMY.TXT DELIMITED. c. Add the dummy records to your original comma delimited data file using the Copy command in DOS, after quitting dBaseII. COPY filename.txt + DUMMY.TXT TO filename.txt. d. Reenter your original data base in dBaseII and find out what was the last area code read into the file earlier (e.g. GO BOTTOM then DISPLAY). e. Append the remaining records from the now-extended comma-delimited file using APPEND FROM filename.txt FOR GEOG8001 > "code", where "code" is the area code for the last record previously appended, e.g., "56041". 3 4. Link area names from a separate file. As originally issued by the Bureau, area names are on a separate file from any of the data. That file can be loaded into dBaseII format using CREATE and APPEND as discussed above. The following example assumes names have been loaded into COUNTY00.DBF. . USE B:COUNTY00 . SELECT SECONDARY . USE B:COUNTY01 . SET LINKAGE ON The last command allows you to work with data for the same area in both files when you ask for any given record in either file, assuming both files feature records for the same areas in the same order. The first file is termed the Primary file, the second the Secondary file. "P." and "S." prefixes are used to tell dBaseII which file to look in. For example to list by name all counties with a population over 1,000,000, type DISPLAY P.GEOG8002, S.POPG8001 FOR S.POPG8001 >= 1000000 .AND. $(S.GEOG8001,3,3) > "000". (The last phrase, beginning with $, eliminates state total records from the display.) Working with two linked files at once can be tricky. For instance, if you have worked with either file before typing SET LINKAGE ON you must first enter GO TOP for both files to ensure that the right records are linked together. A preferred approach would be to extract and merge the desired fields into a single file using UTILITYC prior to entering dBaseII, or in dBaseII using the UPDATE command as described below in item 6. 5. Resorting or ranking records. Sorted lists can be prepared using either SORT or INDEX. Indexing is usually preferred. The following would sort all counties into rank order, then list those with a population over 500,000 in order (identified only by code). . USE B:COUNTY01 . INDEX ON -POPG8001 TO B:POPRANK . USE B:COUNTY01 INDEX B:POPRANK . DISPLAY GEOG8001,POPG8001 FOR POPG8001 > 500000 .AND. $(GEOG8001,3,3) > "000" Note that the indexing variable is presented as a negative. DBaseII indexes only in ascending order, so making the value negative is necessary to get the list in descending order of population. To display area names from a different file, instead of codes, add the following commands: 4 . GO TOP . SELECT SECONDARY . USE B:COUNTY00 INDEX B:POPRANK . DISPLAY S.GEOG8002,P.POPG8001 FOR POPG8001 > 500000 .AND. $(GEOG8001,3,3) > "000" 6. Merge data from multiple files. When you want to use data from more than 2 files at once, or when you are having difficulty linking two files as described in items 4 and 5, CREATE a new data base with all of the desired fields and UPDATE the data into it, as illustrated below. This can be done as long as you have enough space on a single device (diskette or hard disk) to accommodate the merged file, and as long as you do not need to put more than 32 fields into a single file (a dBaseII limitation). . CREATE A COUMERGE FIELD NAME,TYPE,WIDTH,DECIMAL PLACES 001 GEOG8001,C,5 (geographic i.d. on all files) 002 GEOG8301,C,4 003 GEOG8302,C,2 (geographic fields from CCDB00.TXT 004 GEOG8303,C,4 now loaded into COUNTY00.DBF) 005 GEOG8101,C,4 006 GEOG8002,C,30 007 POPG7001,N,9 (selected data from CCDB01.TXT 008 POPG8001,N,9 now loaded into COUNTY01.DBF) 009 FLAG0244,N,1 (selected data from CCDB24.TXT 010 MONY7930,N,5 now loaded into COUNTY24.DBF) 011 _ INPUT DATA NOW? N . USE A:COUMERGE . APPEND FROM B:COUNTY00 . GO TOP . RESET . UPDATE ON GEOG8001 FROM B:COUNTY01 REPLACE POPG7001,POPG8001 . GO TOP . RESET . UPDATE ON GEOG8001 FROM B:COUNTY24 REPLACE FLAG0244,MONY7930 In creating merged files of especially selected items it is tempting to drop all flag fields. In some cases this is quite appropriate. Some items, e.g., total population, have no flags other than zero. In some applications, e.g. ranking areas, there is no way to make use of the flag and the flag can be dropped. This can be accomplished by copying the data to another file using a field list which omits unwanted flags. On the other hand, in routine data display, some flags, such as 4 for suppression for disclosure reasons, are important to show. One alternative is to replace the zero in the data field with the negative of the flag value (e.g., -4) and drop the flag. In subsequent displays it would then 5 be necessary to screen fields for negative values and display a message instead of a data value. This approach must be avoided wherever real negative values may occur (e.g., farm earnings) or where you will want to use the data in calculations without prior screening. 7. Pack data where necessary. In some cases it may seem important to have on one file more than 32 data fields. One way around this dBaseII limitation is to store several items within a single character string. For example, if in step 2 above we had added at the end of the record a 50-character type-C field named MERGEPOP we could pack all the original data items and their flags into that field, which could later be moved to a consolidated data file. For example, . USE B:COUNTY01 . REPLACE ALL MERGEPOP WITH STR(FLAG0011,1) + STR(LAND8001,7) + STR(FLAG0012,1) + STR(POPG7001,9) + ... Any subsequent reference would then require use of the substring function. For example, to reconstruct POPG7001 use VAL($(MERGEPOP,10,9)), i.e., the value of the substring within MERGEPOP beginning with the 10th character and 9 characters in length. Consolidation of merged files into a single data base would require creating a separate data base with fields defined to accept the merged character strings. . CREATE A COUMERGE FIELD NAME,TYPE,WIDTH,DECIMAL PLACES 001 GEOG8001,C,5 002 MERGEPOP,C,50 003 MERGEAGE,C,45 004 _ INPUT DATA NOW? N . USE A:COUMERGE . APPEND FROM B:COUNTY01 . GO TOP . RESET . UPDATE ON GEOG8001 FROM B:COUNTY04 REPLACE MERGEAGE Packing data this way into long character strings is extravagant of disk space, and impractical to use if the original file occupies a large part of available disk space. The only purpose it serves is to circumvent the dBaseII limitation to 32 fields.